from StuManSys import app
from flask import Flask, render_template, request, redirect, url_for, flash
# from .main import db
from .main.model import db, Student, Course, Score
from sqlalchemy import func, case
from .main.form import RegisterForm

db.init_app(app)

# 创建数据库表
with app.app_context():
    db.create_all()

@app.route('/')
def index():
    return render_template('index.html')

@app.route('/register', methods=['GET', 'POST'])
def register():
    register_form = RegisterForm()
    if register_form.handle():
        return redirect(url_for(".login"))
    else:
        return render_template("register.html", form=register_form)

@app.route('/students')
def student_list():
    # 获取所有学生信息
    students = Student.query.all()
    return render_template('students.html', students=students)


@app.route('/student/<int:student_id>')
def student_detail(student_id):
    # 获取特定学生的详细信息
    student = Student.query.get_or_404(student_id)

    # 获取该学生的所有成绩
    scores = db.session.query(
        Score,
        Course
    ).join(
        Course,
        Score.course_id == Course.id
    ).filter(
        Score.student_id == student_id
    ).all()

    # 计算平均分
    avg_score = db.session.query(
        func.avg(Score.score)
    ).filter(
        Score.student_id == student_id
    ).scalar()

    # 计算GPA (假设90-100为4.0，80-89为3.0，以此类推)
    gpa = db.session.query(
        func.avg(
            case([
                (Score.score >= 90, 4.0),
                (Score.score >= 80, 3.0),
                (Score.score >= 70, 2.0),
                (Score.score >= 60, 1.0),
                (Score.score < 60, 0.0)
            ], else_=0.0)
        )
    ).filter(
        Score.student_id == student_id
    ).scalar()

    return render_template('student_detail.html',
                           student=student,
                           scores=scores,
                           avg_score=avg_score,
                           gpa=gpa)


@app.route('/statistics')
def statistics():
    # 班级平均分统计
    class_stats = db.session.query(
        Student.class_name,
        func.avg(Score.score).label('avg_score'),
        func.count(Student.id).label('student_count')
    ).join(
        Score,
        Student.id == Score.student_id
    ).group_by(
        Student.class_name
    ).all()

    # 课程平均分统计
    course_stats = db.session.query(
        Course.name,
        func.avg(Score.score).label('avg_score'),
        func.count(Score.id).label('student_count')
    ).join(
        Score,
        Course.id == Score.course_id
    ).group_by(
        Course.name
    ).all()

    # 分数段统计
    score_ranges = db.session.query(
        func.concat(
            (func.floor(Score.score / 10) * 10).cast(db.String),
            '-',
            (func.floor(Score.score / 10) * 10 + 9).cast(db.String)
        ).label('score_range'),
        func.count(Score.id).label('count')
    ).group_by(
        'score_range'
    ).order_by(
        'score_range'
    ).all()

    return render_template('statistics.html',
                           class_stats=class_stats,
                           course_stats=course_stats,
                           score_ranges=score_ranges)